﻿using System;
using System.Data.SqlClient;
using MySqlConnector;

namespace DbTest
{
    class Program
    {
        const string SqlServerConnectionString = @"Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=XSCJDB;Integrated Security=True";
        //const string MySqlConnectionString = @"data source=localhost;database=xscjdb;user id=root;password=123456;pooling=false;charset=utf8";
        const string MySqlConnectionString = @"data source=119.29.3.216;port=30006;database=xscjdb;user id=root;password=123123;pooling=false;charset=utf8";


        static void Main(string[] args)
        {

            //SqlServer 增删改查
            Console.WriteLine("开始SqlServer 数据库增删改查操作");
            SqlConnection connection = new SqlConnection(SqlServerConnectionString);
            connection.Open();

            string sql = @"drop table if exists students;
create table students(
	id varchar(20) primary key,
	[name] varchar(20),
	sex varchar(5),
	birthday datetime2,
	major varchar(50),
	credit float);";
            Execute(connection, sql);//建表
            AddStudent(connection);  //增加
            UpdateStudent(connection);//更新
            QueryStudent(connection);//查询
            DeleteStudent(connection);//删除
            connection.Close();
            Console.WriteLine("SqlServer 数据库操作执行完毕\n");
            //MySQL增删改查
            Console.WriteLine("开始MySQL 数据库增删改查操作");
            MySqlConnection mySqlConnection = new MySqlConnection(MySqlConnectionString);
            mySqlConnection.Open();
            sql = @"drop table if exists students;
create table students(
	id varchar(20) primary key,
	name varchar(20),
	sex varchar(5),
	birthday datetime,
	major varchar(50),
	credit float);";
            ExecuteMySql(mySqlConnection, sql);//建表
            AddStudentMySql(mySqlConnection);//增加
            UpdateStudentMySql(mySqlConnection);//更新
            QueryStudentMySql(mySqlConnection);//查询
            DeleteStudentMySql(mySqlConnection);//删除
            mySqlConnection.Close();
            Console.WriteLine("MySQL 数据库操作执行完毕\n");
            Console.ReadKey();
        }

        private static void DeleteStudentMySql(MySqlConnection connection)
        {
            Console.Write("请输入要删除的学生学号：");
            string id = Console.ReadLine();
            string sql = $"delete from students where id='{id}'";
            if (ExecuteMySql(connection, sql))
            {
                Console.WriteLine("删除成功");
            }
            else
            {
                Console.WriteLine("删除失败");
            }

            Console.WriteLine("按任意键继续");
            Console.ReadKey();
        }

        private static void QueryStudentMySql(MySqlConnection connection)
        {
            string sql = "select * from students";
            MySqlCommand cmd = connection.CreateCommand();
            cmd.CommandText = sql;
            using var reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                Console.WriteLine($"{reader.GetString(0)} {reader.GetString(1)} {reader.GetString(2)} {reader.GetDateTime(3)} {reader.GetString(4)} {reader.GetDouble(5)} ");
            }
        }

        private static void UpdateStudentMySql(MySqlConnection connection)
        {
            //修改学生数据
            Console.Write("请输入要修改的学生姓名：");
            string oName = Console.ReadLine();
            Console.Write("请输入修改后的学生姓名：");
            string nName = Console.ReadLine();



            string sql = $"update students set name = '{nName}' where name = '{oName}'";
            if (ExecuteMySql(connection, sql))
            {
                Console.WriteLine("修改成功");
            }
            else
            {
                Console.WriteLine("修改失败");
            }

            Console.WriteLine("按任意键继续");
            Console.ReadKey();
        }

        private static void DeleteStudent(SqlConnection connection)
        {
            Console.Write("请输入要删除的学生学号：");
            string id = Console.ReadLine();
            string sql = $"delete from students where id='{id}'";
            if (Execute(connection, sql))
            {
                Console.WriteLine("删除成功");
            }
            else 
            {
                Console.WriteLine("删除失败");
            }

            Console.WriteLine("按任意键继续");
            Console.ReadKey();
        }

        private static void QueryStudent(SqlConnection connection)
        {
            string sql = "select * from students";
            SqlCommand cmd = connection.CreateCommand();
            cmd.CommandText = sql;
            using var reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                Console.WriteLine($"{reader.GetString(0)} {reader.GetString(1)} {reader.GetString(2)} {reader.GetDateTime(3)} {reader.GetString(4)} {reader.GetDouble(5)} ");
            }
        }

        static void AddStudent(SqlConnection connection)
        {
            Console.Write("请输入学号：");
            string id = Console.ReadLine();
            Console.Write("请输入姓名：");
            string name = Console.ReadLine();
            Console.Write("请输入性别：");
            string sex = Console.ReadLine();
            Console.Write("请输入出生日期：");
            string birthday = Console.ReadLine();
            Console.Write("请输入专业：");
            string major = Console.ReadLine();
            Console.Write("请输入总学分：");
            string credit = Console.ReadLine();

            //插入学生数据
            string sql = $"insert into students(id, [name], sex, birthday, major, credit) values('{id}','{name}','{sex}','{birthday}','{major}',{credit} );";
            if (Execute(connection, sql))
            {
                Console.WriteLine("添加成功");
            }
            else
            {
                Console.WriteLine("添加失败");
            }
            Console.WriteLine("按任意键继续");
            Console.ReadKey();
        }

        static void UpdateStudent(SqlConnection connection)
        {
            //修改学生数据
            Console.Write("请输入要修改的学生姓名：");
            string oName = Console.ReadLine();
            Console.Write("请输入修改后的学生姓名：");
            string nName = Console.ReadLine();



            string sql = $"update students set name = '{nName}' where name = '{oName}'";
            if (Execute(connection, sql))
            {
                Console.WriteLine("修改成功");
            }
            else
            {
                Console.WriteLine("修改失败");
            }

            Console.WriteLine("按任意键继续");
            Console.ReadKey();
        }

        static bool Execute(SqlConnection conn, string sql)
        {
            SqlCommand cmd = conn.CreateCommand();
            cmd.CommandText = sql;
            Console.WriteLine(sql);
            int r = cmd.ExecuteNonQuery();
            Console.WriteLine($"受影响的行数:{r}");
            return r > 0;
        }

        static bool ExecuteMySql(MySqlConnection conn, string sql)
        {
            MySqlCommand cmd = conn.CreateCommand();
            cmd.CommandText = sql;
            Console.WriteLine(sql);
            int r = cmd.ExecuteNonQuery();
            Console.WriteLine($"受影响的行数:{r}");
            return r > 0;
        }

        static void AddStudentMySql(MySqlConnection connection)
        {
            Console.Write("请输入学号：");
            string id = Console.ReadLine();
            Console.Write("请输入姓名：");
            string name = Console.ReadLine();
            Console.Write("请输入性别：");
            string sex = Console.ReadLine();
            Console.Write("请输入出生日期：");
            string birthday = Console.ReadLine();
            Console.Write("请输入专业：");
            string major = Console.ReadLine();
            Console.Write("请输入总学分：");
            string credit = Console.ReadLine();

            //插入学生数据
            string sql = $"insert into students(id, name, sex, birthday, major, credit) values('{id}','{name}','{sex}','{birthday}','{major}',{credit} );";
            if (ExecuteMySql(connection, sql))
            {
                Console.WriteLine("添加成功");
            }
            else
            {
                Console.WriteLine("添加失败");
            }
            Console.WriteLine("按任意键继续");
            Console.ReadKey();
        }
    }
}
